﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;
using System.Net.Mail;
using System.Web;
using System.Net;
using System.Configuration;
namespace BAL
{
    public class Users
    {
        public static string con = BAL.conn.connectionString;
        public static string VerifyUserID(string strEmail, string strPassword)
        {
            SqlParameter[] arrParam = new SqlParameter[3];
            arrParam[0] = new SqlParameter("@emailId", SqlDbType.VarChar);
            arrParam[0].Value = strEmail;
            arrParam[1] = new SqlParameter("@password", SqlDbType.VarChar);
            arrParam[1].Value = strPassword;
            string Val = Convert.ToString(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_VerifyUser", arrParam));
            return Val;
        }
        public static int UserRegistration(string firstname, string lastName, string Emailid, string password, int IdCountry)
        {
            SqlParameter[] arrParam = new SqlParameter[5];
            arrParam[0] = new SqlParameter("@Firstname", SqlDbType.VarChar);
            arrParam[0].Value = firstname;
            arrParam[1] = new SqlParameter("@Lastname", SqlDbType.VarChar);
            arrParam[1].Value = lastName;
            arrParam[2] = new SqlParameter("@Email", SqlDbType.VarChar);
            arrParam[2].Value = Emailid;
            arrParam[3] = new SqlParameter("@Password", SqlDbType.VarChar);
            arrParam[3].Value = password;
            arrParam[4] = new SqlParameter("@Idcountry", SqlDbType.Int);
            arrParam[4].Value = IdCountry;

            int IDUser = Convert.ToInt32(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_CreateUser", arrParam));
            return IDUser;

        }
        public static void InsertExperienceDetails(int IdUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IdUser;

            Convert.ToInt32(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_InsertExperienceDetails", arrParam));


        }


        public static string ChangePassword(int UserId, string password, string NewPassword)
        {
            SqlParameter[] arrParam = new SqlParameter[3];
            arrParam[0] = new SqlParameter("@UserID", SqlDbType.Int);
            arrParam[0].Value = UserId;
            arrParam[1] = new SqlParameter("@password", SqlDbType.VarChar);
            arrParam[1].Value = password;
            arrParam[2] = new SqlParameter("@NewPassword", SqlDbType.VarChar);
            arrParam[2].Value = NewPassword;

            string res = VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_changepassword", arrParam).ToString();
            return res;
        }
        public static int OnForgotPassword(string EmailID)
        {
            int intReturnValue = 0;
            string val = VerifyUserID(EmailID, "");

            if (!(val.Equals("-1")))
            {
                string pass = "";
                string Info = "";
                string strName = "User";

                SqlParameter[] arrParam = new SqlParameter[1];
                arrParam[0] = new SqlParameter("@emailid", SqlDbType.VarChar);
                arrParam[0].Value = EmailID;


                DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_forgotpassword", arrParam);
                pass = ds.Tables[0].Rows[0]["Password"].ToString();
                strName = ds.Tables[0].Rows[0]["Name"].ToString();
                if (pass != "")
                {
                    pass = BAL.Encryption.Decrypt(pass, "&%#@?,:*");
                    Info = "<div><span style=\"font-size:12px;\"><br>" +
                    "Dear <b>" + strName + "</b>,<br><br>Please find the details of your " + ConfigurationManager.AppSettings["SiteName"].ToString() + " account :" +
                    "<br><br>Login id : <b>" + EmailID + "</b><br>Password : <b>" + pass +
"<br><br>Best Regards, <br>" + ConfigurationManager.AppSettings["SiteName"].ToString() + " Team<br><a href='http://www.myvocationspace.com'>www." + ConfigurationManager.AppSettings["SiteName"].ToString() + ".com</a></span><br><br><img src='cid:companylogo' style='width:200px;hight:150px'>" +
                "<br><p>Follow Us on </p><div class='addthis_toolbox addthis_32x32_style addthis_default_style'>" +
"<a class='addthis_button_facebook_follow' addthis:userid=''></a>" +
"<a class='addthis_button_twitter_follow' addthis:userid='romstermonster'></a></div>" +
"<script type='text/javascript' src='http://s7.addthis.com/js/250/addthis_widget.js#pubid=ra-4fa12a956ba9df5c'></script>" +
            "<div style=\"float:left;margin:0px 5px 0px 5px\" ><a href='' target='_blank'><img src='cid:fb' style='border:0' /></a></div>" +
            "<div style=\"float:left;margin:0px 5px 0px 5px\" ><a href='' target='_blank'><img src='cid:tweet' style='border:0' /></a></div>";
                    BAL.Common.SendEmail("", EmailID, "Password Recovery", Info);
                    intReturnValue = 1;
                }
            }
            return intReturnValue;
        }
        public static void SendUserDetails(string username, string To_Email_ID, string password)
        {
            string host = HttpContext.Current.Request.Url.Host;
            string content = "<div style=\"margin-left:20px;\">" +
                             "<span style=\"font-size: 12px; font-family: 'CENTURY GOTHIC'\">" +
                             "<br>Dear <b>" + username + "</b>,<br><br>" +
                             "We are pleased to welcome you to the <b>" + ConfigurationManager.AppSettings["SiteName"].ToString() + "</b>  community!<br><br>" +
                             "Your account details are as mentioned below." +
                              "<br>UserID: " + To_Email_ID + "<br>Password: " + password +
                             "<br> Please change your password after login and keep this safely." +
                       "<br><br>Best Regards, <br>" + ConfigurationManager.AppSettings["SiteName"].ToString() + " Team<br><a href='http://www.myvocationspace.com'>www." + ConfigurationManager.AppSettings["SiteName"].ToString() + ".com</a></span><br><br><img src='cid:companylogo' style='width:200px;hiegh:150px'>" +
                "<br><p>Follow Us on </p><div class='addthis_toolbox addthis_32x32_style addthis_default_style'>" +
"<a class='addthis_button_facebook_follow' addthis:userid=''></a>" +
"<a class='addthis_button_twitter_follow' addthis:userid='romstermonster'></a></div>" +
"<script type='text/javascript' src='http://s7.addthis.com/js/250/addthis_widget.js#pubid=ra-4fa12a956ba9df5c'></script>" +
            "<div style=\"float:left;margin:0px 5px 0px 5px\" ><a href='' target='_blank'><img src='cid:fb' style='border:0' /></a></div>" +
            "<div style=\"float:left;margin:0px 5px 0px 5px\" ><a href='' target='_blank'><img src='cid:tweet' style='border:0' /></a></div>";
            string Subject = "User Details";
            BAL.Common.SendEmail("", To_Email_ID, Subject, content);
        }
        public static void RegLink(string To_Email_ID, string CustomerID, string V_str, string firstName)
        {
            if (firstName.Trim() == "") { firstName = "User"; }
            if (firstName != "") { firstName = "<b>" + firstName + "</b>"; }
            string host = HttpContext.Current.Request.Url.Host + "";
            string content = "<div style=\"margin-left:20px;\">" +
                             "<span style=\"font-size: 12px; font-family: 'CENTURY GOTHIC'\">" +
                             "<br>Dear " + firstName + ",<br><br>" +
                             "Warm Welcome from <b>" + ConfigurationManager.AppSettings["SiteName"].ToString() + " team!!!</b> We thank you for showing continued interest in Myvocationspace.<br>" +
                             "You are just one step away from becoming a registered member." +
                             "<br>Kindly click on the link below for email id verification :" +
                             "<br><br>url: <a href=\"http://" + host + "/vocation/Confirmation.aspx?id=" +
                             CustomerID + "&code=" + V_str + " \">Confirm Now</a>" +
                            "<br><br>Best Regards, <br>" + ConfigurationManager.AppSettings["SiteName"].ToString() + " Team<br><a href='http://www.myvocationspace.com'>www." + ConfigurationManager.AppSettings["SiteName"].ToString() + ".com</a></span><br><br><img src='cid:companylogo' style='width:200px;hiegh:150px'>" +
                "<br><p>Follow Us on </p><div class='addthis_toolbox addthis_32x32_style addthis_default_style'>" +
"<a class='addthis_button_facebook_follow' addthis:userid=''></a>" +
"<a class='addthis_button_twitter_follow' addthis:userid='romstermonster'></a></div>" +
"<script type='text/javascript' src='http://s7.addthis.com/js/250/addthis_widget.js#pubid=ra-4fa12a956ba9df5c'></script>" +
            "<div style=\"float:left;margin:0px 5px 0px 5px\" ><a href='' target='_blank'><img src='cid:fb' style='border:0' /></a></div>" +
            "<div style=\"float:left;margin:0px 5px 0px 5px\" ><a href='' target='_blank'><img src='cid:tweet' style='border:0' /></a></div>";


            string Subject = "Confirmation mail From " + ConfigurationManager.AppSettings["SiteName"].ToString() + "";
            BAL.Common.SendEmail("", To_Email_ID, Subject, content);
        }
        public static int UpdateUserProfile(string firstname, string lastname, bool gender, string address,
            string zip,  int IdCountry, string Occupation,
            //string Org, string CurrPos,  
            string City,  string Photo,
            //string Education, string education2, string education3,string workexp, 
            int IdUser)
        {
            SqlParameter[] arrParam = new SqlParameter[10];
            arrParam[0] = new SqlParameter("@Firstname", SqlDbType.VarChar);
            arrParam[0].Value = firstname;
            arrParam[1] = new SqlParameter("@lastname", SqlDbType.VarChar);
            arrParam[1].Value = lastname;
            arrParam[2] = new SqlParameter("@gender", SqlDbType.Bit);
            arrParam[2].Value = gender;
            arrParam[3] = new SqlParameter("@address", SqlDbType.VarChar);
            arrParam[3].Value = address;
            arrParam[4] = new SqlParameter("@zip", SqlDbType.VarChar);
            arrParam[4].Value = zip;

            //arrParam[5] = new SqlParameter("@mobNo", SqlDbType.VarChar);
            //arrParam[5].Value = mobile;
            arrParam[5] = new SqlParameter("@Idcountry", SqlDbType.Int);
            arrParam[5].Value = IdCountry;
            //arrParam[6] = new SqlParameter("@IDIndustry", SqlDbType.Int);
            //arrParam[6].Value = IDIndustry;
            arrParam[6] = new SqlParameter("@occupation", SqlDbType.VarChar);
            arrParam[6].Value = Occupation;

         
            //arrParam[7] = new SqlParameter("@Orgnization", SqlDbType.VarChar);
            //arrParam[7].Value = Org;
            //arrParam[8] = new SqlParameter("@CurrentPosition", SqlDbType.VarChar);
            //arrParam[8].Value = CurrPos;
            //arrParam[9] = new SqlParameter("@Education", SqlDbType.VarChar);
            //arrParam[9].Value = Education;
            arrParam[7] = new SqlParameter("@City", SqlDbType.VarChar);
            arrParam[7].Value = City;
            arrParam[8] = new SqlParameter("@Photo", SqlDbType.VarChar);
            arrParam[8].Value = Photo;
            //arrParam[13] = new SqlParameter("@Education2", SqlDbType.VarChar);
            //arrParam[13].Value = education2;
            //arrParam[15] = new SqlParameter("@Education3", SqlDbType.VarChar);
            //arrParam[15].Value = education3;
            //arrParam[16] = new SqlParameter("@workExp", SqlDbType.VarChar);
            //arrParam[16].Value = workexp;

            arrParam[9] = new SqlParameter("@iduser", SqlDbType.Int);
            arrParam[9].Value = IdUser;


            int res = Convert.ToInt32(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_UpdateUserProfile", arrParam));

            return res;
        }



        public static void InsertEducation(int IDUser, string Course, string College, string Year)
        {
            SqlParameter[] arrParam = new SqlParameter[4];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;
            arrParam[1] = new SqlParameter("@Course", SqlDbType.VarChar);
            arrParam[1].Value = Course;
            arrParam[2] = new SqlParameter("@College", SqlDbType.VarChar);
            arrParam[2].Value = College;
            arrParam[3] = new SqlParameter("@Year", SqlDbType.VarChar);
            arrParam[3].Value = Year;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_InsertUserCourse", arrParam);
        }


        public static bool GetAdminFlag(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;

            bool adminflag = Convert.ToBoolean(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_IsAdmin", arrParam));
            return adminflag;
        }

        public static string[] GetUserDetails(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@iduser", SqlDbType.Int);
            arrParam[0].Value = IDUser;

            SqlDataReader sdr = VocationSpace.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "VS_GetUserDetails", arrParam);
            int count = sdr.FieldCount;
            string[] details = new string[count];
            while (sdr.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    details[i] = sdr[i].ToString();
                }
            }
            sdr.Close();
            return details;
        }


      

        public static string[] GetUserEditByID(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;

            SqlDataReader sdr = VocationSpace.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "VS_GetUserEditByID", arrParam);
            int count = sdr.FieldCount;
            string[] details = new string[count];
            while (sdr.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    details[i] = sdr[i].ToString();
                }
            }
            sdr.Close();
            return details;
        }


        public static void ConfirmUser(int Customer_ID, string Verifi_Code)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = Customer_ID;
            arrParam[1] = new SqlParameter("@Verifi_Str", SqlDbType.VarChar);
            arrParam[1].Value = Verifi_Code;
            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_ConfirmUser", arrParam);
        }
        public static DataSet GetCustomerReviewById(int IDCustomer, int Type)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IdCustomer", SqlDbType.Int);
            arrParam[0].Value = IDCustomer;
            arrParam[1] = new SqlParameter("@type", SqlDbType.Int);
            arrParam[1].Value = Type;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "CM_GetReviewsByCustomer", arrParam);
            return ds;

        }
        public static void UploadProfilepic(int IdUser, string Image)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IdUser", SqlDbType.Int);
            arrParam[0].Value = IdUser;
            arrParam[1] = new SqlParameter("@Image", SqlDbType.VarChar);
            arrParam[1].Value = Image;
            int res = VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "CM_UploadProfilePic", arrParam);

        }
        public static string GetProfilePic(int UserId)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IdUser", SqlDbType.Int);
            arrParam[0].Value = UserId;
            string Res = VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "CM_GetUserPicture", arrParam).ToString();
            return Res;

        }
        public static void InsertNewsletterReq(string EmailId)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@Email", SqlDbType.VarChar);
            arrParam[0].Value = EmailId;
            int res = VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "CM_InsertNewsletterReq", arrParam);
        }
        public static DataSet GetFavoriteTopics(int IdUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IdCustomer", SqlDbType.Int);
            arrParam[0].Value = IdUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "CM_GetFavoriteRestaurant", arrParam);
            return ds;
        }
        public static DataSet GetRecentTopics(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDCustomer", SqlDbType.Int);
            arrParam[0].Value = IDUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "CM_GetRecentOrdersByCustomer", arrParam);
            return ds;

        }
        public static void UpdateLastLogin(int Iduser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@iduser", SqlDbType.Int);
            arrParam[0].Value = Iduser;

            int res = VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_UpdateLastLogin", arrParam);

        }
        public static string GetLastLoginDate(int Iduser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@iduser", SqlDbType.Int);
            arrParam[0].Value = Iduser;

            string res = Convert.ToString(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_GetLastLoginDate", arrParam));
            return res;
        }

        public static void AddFavouriteTopic(int Iduser, int Idtopic)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@iduser", SqlDbType.Int);
            arrParam[0].Value = Iduser;
            arrParam[1] = new SqlParameter("@idtopic", SqlDbType.VarChar);
            arrParam[1].Value = Idtopic;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_AddFavouriteTopic", arrParam);
        }


        public static void PopulateMyVocation(ref DropDownList ddlMyVocation, int IdUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IdUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetVocationByUser", arrParam);

            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlMyVocation.DataSource = ds.Tables[0];

                    ddlMyVocation.DataTextField = "VocationName";
                    ddlMyVocation.DataValueField = "IDVocation";
                    ddlMyVocation.DataBind();

                    ListItem lst = new ListItem("-----All Vocations-----", "-1");
                    ddlMyVocation.Items.Insert(0, lst);

                    foreach (ListItem _listItem in ddlMyVocation.Items)
                    {

                        _listItem.Attributes.Add("title", _listItem.Text + _listItem.Value);

                    }

                }

            }
        }


        public static void GetCountryByUser(ref DropDownList ddlCountry, int IdUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IdUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, "VS_GetCountryByUser", arrParam);
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlCountry.DataSource = ds.Tables[0];
                    ddlCountry.DataTextField = "CountryName";
                    ddlCountry.DataValueField = "IDCountry";
                    ddlCountry.DataBind();

                    ListItem lst = new ListItem("---Select Country---", "-1");
                    ddlCountry.Items.Insert(0, lst);

                    foreach (ListItem _listItem in ddlCountry.Items)
                    {

                        _listItem.Attributes.Add("title", _listItem.Text + _listItem.Value);

                    }

                }
                else
                {
                    ddlCountry.Items.Clear();
                    ListItem lst = new ListItem("---Select Country---", "-1");
                    ddlCountry.Items.Insert(0, lst);
                }
            }
        }

        

        public static void UpdateExperienceDetails(string Designation2,string orgnisation2,int IDIndustry2,string Experience2,string Designation3,string orgnisation3,int IDIndustry3,string Experience3,int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[9];
            arrParam[0] = new SqlParameter("@Designation2", SqlDbType.VarChar);
            arrParam[0].Value = Designation2;
            arrParam[1] = new SqlParameter("@orgnisation2", SqlDbType.VarChar);
            arrParam[1].Value = orgnisation2;
            arrParam[2] = new SqlParameter("@IDIndustry2", SqlDbType.Int);
            arrParam[2].Value = IDIndustry2;
            arrParam[3] = new SqlParameter("@Experience2", SqlDbType.VarChar);
            arrParam[3].Value = Experience2;
            arrParam[4] = new SqlParameter("@Designation3", SqlDbType.VarChar);
            arrParam[4].Value = Designation3;
            arrParam[5] = new SqlParameter("@orgnisation3", SqlDbType.VarChar);
            arrParam[5].Value = orgnisation3;
            arrParam[6] = new SqlParameter("@IDIndustry3", SqlDbType.Int);
            arrParam[6].Value = IDIndustry3;
            arrParam[7] = new SqlParameter("@Experience3", SqlDbType.VarChar);
            arrParam[7].Value = Experience3;
            arrParam[8] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[8].Value = IDUser;

            VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "UpdateExperienceDetails", arrParam);

        }


        public static DataSet GetVocationByUser(int IDUSer)
        {

            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUSer;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetVocationByUser", arrParam);
            return ds;
        }



        public static int GetConnectionByUser(int Iduser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@iduser", SqlDbType.Int);
            arrParam[0].Value = Iduser;
            int count = Convert.ToInt32(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_GetConnectionByUser", arrParam));
            return count;
        }
        public static DataSet MembersByVocation(int IdUser, int IDCountry, int IDVocation, int IDIndustry)
        {
            SqlParameter[] arrParam = new SqlParameter[4];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IdUser;

            arrParam[1] = new SqlParameter("@IDCountry", SqlDbType.Int);

            if (IDCountry > 0)
                arrParam[1].Value = IDCountry;
            else
                arrParam[1].Value = System.DBNull.Value;
            arrParam[2] = new SqlParameter("@IDVocation", SqlDbType.Int);
            if (IDVocation > 0)
                arrParam[2].Value = IDVocation;
            else
                arrParam[2].Value = System.DBNull.Value;

            arrParam[3] = new SqlParameter("@IDIndustry", SqlDbType.Int);
            arrParam[3].Value = IDIndustry;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetUserOfMyVocationByID", arrParam);

            return ds;
        }

        public static DataSet GetTopicOfMyVocation(int IdUser, string strSearch, int IDIndustry, int IDVocation, int IDCountry)
        {
            SqlParameter[] arrParam = new SqlParameter[5];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IdUser;
            arrParam[1] = new SqlParameter("@strSearch", SqlDbType.VarChar);
            arrParam[1].Value = strSearch;
            arrParam[2] = new SqlParameter("@IDIndustry", SqlDbType.Int);
            arrParam[2].Value = IDIndustry;

            arrParam[3] = new SqlParameter("@IDVocation", SqlDbType.Int);
            //if (IDVocation > 0)
                arrParam[3].Value = IDVocation;
            //else
            //    arrParam[3].Value = System.DBNull.Value;
         
            arrParam[4] = new SqlParameter("@IDCountry", SqlDbType.Int);
            arrParam[4].Value = IDCountry;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetTopicOfMyVocation", arrParam);

            return ds;
        }

        public static DataSet GetPeopleByName(string searchName, int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@searchtext", SqlDbType.VarChar);
            arrParam[0].Value = searchName;
            arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[1].Value = IDUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetPeopleByName", arrParam);

            return ds;
        }

        public static void InsertUserConnection(int IDConnection, int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IDConnection", SqlDbType.Int);
            arrParam[0].Value = IDConnection;
            arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[1].Value = IDUser;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_InsertUserConnection", arrParam);

        }

        public static void InsertUserMentor(int IDMentor, int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IDMentor", SqlDbType.Int);
            arrParam[0].Value = IDMentor;
            arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[1].Value = IDUser;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_InsertUserMentor", arrParam);
        }

        public static void UpdateUserConnectionforAccept(int IDConnection, int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IDConnection", SqlDbType.Int);
            arrParam[0].Value = IDConnection;
            arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[1].Value = IDUser;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_UpdateUserConnectionforAccept", arrParam);
        }

        public static void UpdateUserConnectionforReject(int IDConnection, int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IDConnection", SqlDbType.Int);
            arrParam[0].Value = IDConnection;
            arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[1].Value = IDUser;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_UpdateUserConnectionforReject", arrParam);
        }

        public static void UpdateUserMentorforAccept(int IDMentor, int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IDMentor", SqlDbType.Int);
            arrParam[0].Value = IDMentor;
            arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[1].Value = IDUser;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_UpdateUserMentorforAccept", arrParam);
        }

        public static void UpdateUserMentorforReject(int IDMentor, int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IDMentor", SqlDbType.Int);
            arrParam[0].Value = IDMentor;
            arrParam[1] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[1].Value = IDUser;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_UpdateUserMentorforReject", arrParam);
        }

        public static DataSet GetConnetionDetailByUSer(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetConnetionDetailByUSer", arrParam);
            return ds;
        }

        public static DataSet GetPendingConDetailByUSer(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDConnection", SqlDbType.Int);
            arrParam[0].Value = IDUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetPendingConDetailByUSer", arrParam);
            return ds;
        }



        public static DataSet GetPendingMentisDetailByUser(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDMentor", SqlDbType.Int);
            arrParam[0].Value = IDUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetPendingMentisDetailByUser", arrParam);
            return ds;
        }

        public static DataSet GetMyMentorDetailByUser(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetMyMentorDetailByUser", arrParam);
            return ds;
        }

        public static DataSet GetMyMentisDetailByUser(int IDMentor)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDMentor", SqlDbType.Int);
            arrParam[0].Value = IDMentor;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetMyMentisDetailByUser", arrParam);
            return ds;
        }

        public static void PopulateMyMentor(ref DropDownList ddlMyMentor, int IdUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IdUser;

            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetMentorNameByUser", arrParam);

            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    ddlMyMentor.DataSource = ds.Tables[0];

                    ddlMyMentor.DataTextField = "Name";  // +' ' + "Lastname";
                    ddlMyMentor.DataValueField = "IDUser";
                    ddlMyMentor.DataBind();

                    ListItem lst = new ListItem("---Select Mentor---", "-1");
                    ddlMyMentor.Items.Insert(0, lst);

                    foreach (ListItem _listItem in ddlMyMentor.Items)
                    {

                        _listItem.Attributes.Add("title", _listItem.Text + _listItem.Value);

                    }
                }
            }
        }

        public static DataSet  GetIndustryByUser(int IdUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IdUser;
            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetIndustryByUsers", arrParam);
            return ds;
        }

        public static DataSet GetAllUserCourseByUserID(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;
            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetAllUserCourseByUserID", arrParam);
            return ds;
        }

        public static string[] GetUserCourseByUserCourseID(int IDUserCourse)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUserCourse", SqlDbType.Int);
            arrParam[0].Value = IDUserCourse;

            SqlDataReader sdr = VocationSpace.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "VS_GetUserCourseByUserCourseID", arrParam);
            int count = sdr.FieldCount;
            string[] details = new string[count];
            while (sdr.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    details[i] = sdr[i].ToString();
                }
            }
            sdr.Close();
            return details;
        }


        public static void UpdateUserCourse(int IDUserCourse, string Course, string College, string Year)
        {
            SqlParameter[] arrParam = new SqlParameter[4];
            arrParam[0] = new SqlParameter("@IDUserCourse", SqlDbType.Int);
            arrParam[0].Value = IDUserCourse;
            arrParam[1] = new SqlParameter("@Course", SqlDbType.VarChar);
            arrParam[1].Value = Course;
            arrParam[2] = new SqlParameter("@College", SqlDbType.VarChar);
            arrParam[2].Value = College;
            arrParam[3] = new SqlParameter("@Year", SqlDbType.VarChar);
            arrParam[3].Value = Year;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_UpdateUserCourse", arrParam);
        }

        public static void InsertUserExperience(int IDUser, string Designation, string Orgnization, string Location, string YearFrom, string YearTo)
        {
            SqlParameter[] arrParam = new SqlParameter[6];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;
            arrParam[1] = new SqlParameter("@Designation", SqlDbType.VarChar);
            arrParam[1].Value = Designation;
            arrParam[2] = new SqlParameter("@Orgnization", SqlDbType.VarChar);
            arrParam[2].Value = Orgnization;
            arrParam[3] = new SqlParameter("@Location", SqlDbType.VarChar);
            arrParam[3].Value = Location;
            arrParam[4] = new SqlParameter("@YearFrom", SqlDbType.VarChar);
            arrParam[4].Value = YearFrom;
            arrParam[5] = new SqlParameter("@YearTo", SqlDbType.VarChar);
            arrParam[5].Value = YearTo;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_InsertUserExperience", arrParam);
        }

        public static void UpdateUserExperience(int IDUserExperience, string Designation, string Orgnization, string Location, string YearFrom, string YearTo)
        {
            SqlParameter[] arrParam = new SqlParameter[6];
            arrParam[0] = new SqlParameter("@IDUserExperience", SqlDbType.Int);
            arrParam[0].Value = IDUserExperience;
            arrParam[1] = new SqlParameter("@Designation", SqlDbType.VarChar);
            arrParam[1].Value = Designation;
            arrParam[2] = new SqlParameter("@Orgnization", SqlDbType.VarChar);
            arrParam[2].Value = Orgnization;
            arrParam[3] = new SqlParameter("@Location", SqlDbType.VarChar);
            arrParam[3].Value = Location;
            arrParam[4] = new SqlParameter("@YearFrom", SqlDbType.VarChar);
            arrParam[4].Value = YearFrom;
            arrParam[5] = new SqlParameter("@YearTo", SqlDbType.VarChar);
            arrParam[5].Value = YearTo;

            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_UpdateUserExperience", arrParam);
        }


        public static DataSet GetAllUserExperienceByUserID(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;
            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetAllUserExperienceByUserID", arrParam);
            return ds;
        }

        public static string[] GetUserExperienceByUserExperienceID(int IDUserExperience)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUserExperience", SqlDbType.Int);
            arrParam[0].Value = IDUserExperience;

            SqlDataReader sdr = VocationSpace.DAL.SqlHelper.ExecuteReader(con, CommandType.StoredProcedure, "VS_GetUserExperienceByUserExperienceID", arrParam);
            int count = sdr.FieldCount;
            string[] details = new string[count];
            while (sdr.Read())
            {
                for (int i = 0; i < count; i++)
                {
                    details[i] = sdr[i].ToString();
                }
            }
            sdr.Close();
            return details;
        }

        public static DataSet GetHigherCourseByUserID(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;
            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetHigherCourseByUserID", arrParam);
            return ds;
        }

        public static DataSet GetCurrentExpByUserID(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;
            DataSet ds = VocationSpace.DAL.SqlHelper.ExecuteDataset(con, CommandType.StoredProcedure, "VS_GetCurrentExpByUserID", arrParam);
            return ds;
        }


        public static void DeleteUserCourse(int IDUserCourse)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUserCourse", SqlDbType.Int);
            arrParam[0].Value = IDUserCourse;
            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_DeleteUserCourse", arrParam);
           
        }

        public static void DeleteUserExperience(int IDUserExperience)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUserExperience", SqlDbType.Int);
            arrParam[0].Value = IDUserExperience;
            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_DeleteUserExperience", arrParam);

        }

        public static void UpdateUserPhoto(int IDUser, string Photo)
        {
            SqlParameter[] arrParam = new SqlParameter[2];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;
            arrParam[1] = new SqlParameter("@Photo", SqlDbType.VarChar);
            arrParam[1].Value = Photo;
            VocationSpace.DAL.SqlHelper.ExecuteNonQuery(con, CommandType.StoredProcedure, "VS_UpdateUserPhoto", arrParam);
        }

        public static int HasIndustry(int IDUser)
        {
            SqlParameter[] arrParam = new SqlParameter[1];
            arrParam[0] = new SqlParameter("@IDUser", SqlDbType.Int);
            arrParam[0].Value = IDUser;
            int flag = Convert.ToInt32(VocationSpace.DAL.SqlHelper.ExecuteScalar(con, CommandType.StoredProcedure, "VS_HasIndustry", arrParam));
            return flag;
        }

       
    }
}
